package com.read.common;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.read.utils.DBUtils;
import com.read.utils.StringUtils;

public class BaseDao <T>{
	private static final Logger log = LoggerFactory.getLogger(BaseDao.class);
	private Connection con = null;
	private PreparedStatement pst= null;
	private ResultSet rs= null;
	
	/**
	 * 查询返回对象list
	 * @param sql
	 * @param entity
	 * @param prepares
	 * @return
	 */
	@SuppressWarnings("unchecked")
	protected List<T> getList(String sql,T entity,String... prepares ){
		List<T> list =  new ArrayList<T>();
		try {
			con = DBUtils.getCon();
			pst = con.prepareStatement(sql);
			if(prepares!= null && prepares.length>0){
				for(int i=1;i<prepares.length;i++){
					pst.setString(i, prepares[i]);
				}
			}
 			
			rs = pst.executeQuery();
			
			ResultSetMetaData data = rs.getMetaData();
			PropertyDescriptor pro = null;
			int num = data.getColumnCount();
			while(rs.next()){
				Object obj = entity.getClass().newInstance();
				String clumnName = null;
				for(int index =1;index<=num;index++){
					clumnName = data.getColumnName(index).toLowerCase();
					//log.debug("数据库字段:{}, 对应属性：{}, 值：{}",clumnName,StringUtils.toCamelCase(clumnName),rs.getObject(StringUtils.toUnderlineName(clumnName)));
					pro = new PropertyDescriptor(StringUtils.toCamelCase(clumnName), entity.getClass());
					Method md=pro.getWriteMethod();
					//log.debug("set 方法：{}",md.getName());
					md.invoke(obj, rs.getObject(clumnName));
				}
				
				list.add((T)obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IntrospectionException e) {
			e.printStackTrace();
		}catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}finally {
			DBUtils.dbClear(rs, pst, con);
		}
		
		return list;
	}
	/**
	 * 查询返回map
	 * @param sql
	 * @param prepares
	 * @return
	 */
	protected List<Map<String,Object>> getList(String sql,String... prepares ){
		List<Map<String,Object>> list =  new ArrayList<Map<String,Object>>();
		Map<String,Object> map = null;
		try {
			con = DBUtils.getCon();
			pst = con.prepareStatement(sql);
			
			if(prepares!= null && prepares.length>0){
				for(int i =1;i<=prepares.length;i++){
					pst.setString(i, prepares[i]);
				}
			}
			rs = pst.executeQuery();
			
			ResultSetMetaData data = rs.getMetaData();
			
			int num = data.getColumnCount();
			while(rs.next()){
				String clumnName = null;
				map = new HashMap<String, Object>();
				for(int i = 1;i<=num;i++){
					clumnName = data.getColumnName(i).toLowerCase();
					log.debug("属性：{} 值：{}",StringUtils.toCamelCase(clumnName),rs.getObject(clumnName));
					map.put(StringUtils.toCamelCase(clumnName),rs.getObject(clumnName) );
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}  catch (IllegalArgumentException e) {
			e.printStackTrace();
		} finally {
			DBUtils.dbClear(rs, pst, con);
		}
		
		return list;
	}
	/**
	 * 新增
	 * @param tableName 表名
	 * @param entity	实体对象
	 */
	protected void save(String tableName,T entity) {
		
		try{
			Field[] fds = entity.getClass().getDeclaredFields();
			Field.setAccessible(fds, true); 
			String sql = getInsertSql(tableName, fds);
			log.debug("insert sql : [{}]",sql);
			con = DBUtils.getCon();
			pst = con.prepareStatement(sql);
			StringBuffer sb = new StringBuffer();
			for(int i = 1;i<=fds.length;i++){
				pst.setObject(i, fds[i-1].get(entity));
				sb.append(fds[i-1].get(entity)).append(",");
			}
			log.debug("preparedStatement : [{}]",sb.deleteCharAt(sb.length()-1));
			int num = pst.executeUpdate();
			log.debug("execute result :{}",num);;
		}catch(IllegalAccessException e){
			e.printStackTrace();
		}catch (IllegalArgumentException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.dbClear(rs, pst, con);
		}
	}
	
	/**
	 * 修改
	 * @param tableName 表名
	 * @param pk	主健名
	 * @param entity	实体对象
	 */
	protected void updateByPK(String tableName,String pk,T entity){
		StringBuffer sql = new StringBuffer();
		Field[] fds = entity.getClass().getDeclaredFields();
		Field.setAccessible(fds, true);
		sql.append("update ").append(tableName).append(" set ");
		try{
			int flag = 1;
			Object value = null;
			for(int i = 0; i<fds.length;i++){
				if(StringUtils.toUnderlineName(fds[i].getName()).equals(pk)){
					value=fds[i].get(entity);
					continue;
				}
				if(fds[i].get(entity) == null || fds[i].get(entity).toString().equals("")){
					continue;
				}
				sql.append(StringUtils.toUnderlineName(fds[i].getName())).append(" = ?,");
				flag ++;
			}
			log.debug("flag:{}",flag);
			sql.deleteCharAt(sql.length()-1);
			sql.append(" where ").append(pk).append(" = ?");
			log.debug("update sql : [{}]",sql);
			con = DBUtils.getCon();
			pst = con.prepareStatement(sql.toString());
			StringBuffer sb = new StringBuffer();
			
			for(int i = 1, j=1; i<=fds.length;i++){
				//log.debug("name:[{}],vlaue:[{}]",StringUtils.toUnderlineName(fds[i-1].getName()),fds[i-1].get(entity));
				if(StringUtils.toUnderlineName(fds[i-1].getName()).equals(pk)){
					continue;
				}
				if(fds[i-1].get(entity) == null || fds[i-1].get(entity).toString().equals("")){
					continue;
				}
				String type = fds[i-1].getType().getName();
				type=type.substring(type.lastIndexOf(".")+1);
				pst.setObject(j, fds[i-1].get(entity));
				sb.append(fds[i-1].get(entity)).append("(")
				.append(type)
				.append("),");
				j++;
				
			}
			pst.setObject(flag, value);
			sb.append(value);
			log.debug("preparedStatement : [{}]",sb);
			int num = pst.executeUpdate();
			log.debug("excute result:{}",num);
		}catch(IllegalAccessException e){
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.dbClear(rs, pst, con);
		}
	}
	
	/**
	 * 获取新增数据sql
	 * @param tableName
	 * @param fds
	 * @return
	 */
	private String getInsertSql(String tableName,Field[] fds){
		StringBuffer sql = new StringBuffer();
		sql.append("insert into ").append(tableName).append(" ( ");
		for(Field fd : fds){
			sql.append(StringUtils.toUnderlineName(fd.getName())).append(",");
		}
		sql.deleteCharAt(sql.length()-1).append(" ) values ( ");
		
		for(int i= 0; i<fds.length; i++){
			sql.append(" ?,");
		}
		sql.deleteCharAt(sql.length()-1).append(" ) ");
		return sql.toString();
		
	}
	
	
}
